SQL用法总结

SQL 用法–postgresql

[TOC]

运算符

https://www.postgresql.org/docs/9.1/static/functions-geometry.html

官方链接最全。

表格操作

建表

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY, //声明主键
    username VARCHAR(255) NOT NULL UNIQUE,  //声明非空，唯一
    password VARCHAR(255) NOT NULL 
    FOREIGN KEY (user_id)  
    REFERENCES projects (user_id)
    //声明  user_id为外键引用自表格projects
);

改表

ALTER TABLE table_name
ADD new_colum data_type column_constraint [AFTER existing_column];  // 加列

DROP COLUMN max_limit,  //删除列

MODIFY fee NUMERIC (10, 2) NOT NULL;  //修改列约束

删表

TRUNCATE table_name; // 将行清空，但表头依旧保留

DROP table table_name; //删除整个表格

	PRIMARY KEY constraint	UNIQUE constraint
The number of constraints	One	Many
NULL values	Do not allow	Allow

表的连接

内连接–INNER JOIN

两个表使用内连接，通过指定一个属性来匹配，若两个表的同一属性具有相同值，则保留在连接表中，其余不相等的行则消去。

图解

使用示例（内连接也可以连接多张表格）

SELECT
  A.n
FROM A
INNER JOIN B ON B.n = A.n;
INNER JOIN C ON C.n = A.n;

全连接–FUll OUTER JOIN

指定连接条件，属性值相同的行保留，但不重复，属性值不同的值保留，但存在某些为Null的情况。

用法示例

1
2
3

SELECT column_list
FROM A
FULL OUTER JOIN B ON B.n = A.n;

左连接–LEFT JOIN

对几个将要连接的表指定条件进行连接，属性匹配的时候，会保留左表所有属性，即便右表属性为NULL,但不会存在左表属性为NULL的情况。

代码示例：

SELECT
    A.n
FROM
    A
LEFT JOIN B ON B.n = A.n;

右连接–RIGHT JOIN

和左连接相反，用法如下：

SELECT
    A.n
FROM
    A
LEFT JOIN B ON B.n = A.n;

交叉连接–CROSS JOIN

看图就知道。。。交叉连接结果的表格是两表行数相乘。需要指出的一点就是两个表相连不需要指定cross join。

SELECT 
    column_list
FROM
    A,
    B;

创建索引

PostgreSQL provides several index types: B-tree, Hash, GiST and GIN. Each index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations.

创建b_tree索引

语法： create index index_name ON table_name(column_name)

postgresql默认b树索引。官方描述如下：

B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators:

<

<=

=

>=

>

Constructs equivalent to combinations of these operators, such as

BETWEEN

and

IN

, can also be implemented with a B-tree index search. Also, an

IS NULL

or

IS NOT NULL

condition on an index column can be used with a B-tree index.

`<`
`<=`
`=`
`>=`
`>`

上述说明b_tree在范围查询中比较有效，也可以用于顺序检索数据。

当查询条件为范围查询时(运算符是 > < <= >= = is null, is not null, between and ,in)，b_tree索引可以体现较为良好的性能。

创建hash索引

语法： create index index_name on table_name using hash(column_name)

官方描述

Hash indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the = operator.

上述说明，hash索引仅仅在处理简单的等值比较-(=) 中相对有效。

使用GiST索引

(错误）语法： create index index_name on table_name using GiST（column_name)

官方描述

GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be used vary depending on the indexing strategy (the operator class). As an example, the standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:

<<

&<

&>

>>

`<< `

`&< `

` &>`

` >>`

@>

<@

~=

&&

`<<`
`&<`
`&>`
`>>`
`<<	`
`&<	`
`	&>`
`	>>`
`@>`
`<@`
`~=`
`&&`

上述描述可知，gist 不是单一类型的索引，实现了不同策略索引的基础结构。可以根据索引策略（运算符类）来采取不同的索引策略。

详见官方文档gist index

使用GIN索引

gin索引能够用于优化级的最近邻查找。

GIN indexes are inverted indexes which can handle values that contain more than one key, arrays for example. Like GiST, GIN can support many different user-defined indexing strategies and the particular operators with which a GIN index can be used vary depending on the indexing strategy. As an example, the standard distribution of PostgreSQLincludes GIN operator classes for one-dimensional arrays, which support indexed queries using these operators:

<@

@>

=

&&

`<@`
`@>`
`=`
`&&`

用法查看官方描述gin index.

分析效率 explain

将explain(analyze)置于语句的开始，结果将返回语句执行效率有关的数值。


explain （analyze） Select s_name, s_address, s_nationkey 
From table_a 
Where s_suppkey = 717;

explain （analyze） Select s_name, s_address, s_nationkey 
From b_tree_a 
Where s_suppkey = 717;

explain （analyze） Select s_name, s_address, s_nationkey 
From hash_a 
Where s_suppkey = 717;

Click To Expand Code
PostgreSQL
The following script creates the HR sample database structure in PostgreSQL.


CREATE TABLE regions (
	region_id SERIAL PRIMARY KEY,
	region_name CHARACTER VARYING (25)
);

CREATE TABLE countries (
	country_id CHARACTER (2) PRIMARY KEY,
	country_name CHARACTER VARYING (40),
	region_id INTEGER NOT NULL,
	FOREIGN KEY (region_id) REFERENCES regions (region_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE locations (
	location_id SERIAL PRIMARY KEY,
	street_address CHARACTER VARYING (40),
	postal_code CHARACTER VARYING (12),
	city CHARACTER VARYING (30) NOT NULL,
	state_province CHARACTER VARYING (25),
	country_id CHARACTER (2) NOT NULL,
	FOREIGN KEY (country_id) REFERENCES countries (country_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE departments (
	department_id SERIAL PRIMARY KEY,
	department_name CHARACTER VARYING (30) NOT NULL,
	location_id INTEGER,
	FOREIGN KEY (location_id) REFERENCES locations (location_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE jobs (
	job_id SERIAL PRIMARY KEY,
	job_title CHARACTER VARYING (35) NOT NULL,
	min_salary NUMERIC (8, 2),
	max_salary NUMERIC (8, 2)
);

CREATE TABLE employees (
	employee_id SERIAL PRIMARY KEY,
	first_name CHARACTER VARYING (20),
	last_name CHARACTER VARYING (25) NOT NULL,
	email CHARACTER VARYING (100) NOT NULL,
	phone_number CHARACTER VARYING (20),
	hire_date DATE NOT NULL,
	job_id INTEGER NOT NULL,
	salary NUMERIC (8, 2) NOT NULL,
	manager_id INTEGER,
	department_id INTEGER,
	FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments (department_id) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE dependents (
	dependent_id SERIAL PRIMARY KEY,
	first_name CHARACTER VARYING (50) NOT NULL,
	last_name CHARACTER VARYING (50) NOT NULL,
	relationship CHARACTER VARYING (25) NOT NULL,
	employee_id INTEGER NOT NULL,
	FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE regions (
    region_id SERIAL PRIMARY KEY,
    region_name CHARACTER VARYING (25)
);
 
CREATE TABLE countries (
    country_id CHARACTER (2) PRIMARY KEY,
    country_name CHARACTER VARYING (40),
    region_id INTEGER NOT NULL,
    FOREIGN KEY (region_id) REFERENCES regions (region_id) ON UPDATE CASCADE ON DELETE CASCADE
);
 
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    street_address CHARACTER VARYING (40),
    postal_code CHARACTER VARYING (12),
    city CHARACTER VARYING (30) NOT NULL,
    state_province CHARACTER VARYING (25),
    country_id CHARACTER (2) NOT NULL,
    FOREIGN KEY (country_id) REFERENCES countries (country_id) ON UPDATE CASCADE ON DELETE CASCADE
);
 
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name CHARACTER VARYING (30) NOT NULL,
    location_id INTEGER,
    FOREIGN KEY (location_id) REFERENCES locations (location_id) ON UPDATE CASCADE ON DELETE CASCADE
);
 
CREATE TABLE jobs (
    job_id SERIAL PRIMARY KEY,
    job_title CHARACTER VARYING (35) NOT NULL,
    min_salary NUMERIC (8, 2),
    max_salary NUMERIC (8, 2)
);
 
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name CHARACTER VARYING (20),
    last_name CHARACTER VARYING (25) NOT NULL,
    email CHARACTER VARYING (100) NOT NULL,
    phone_number CHARACTER VARYING (20),
    hire_date DATE NOT NULL,
    job_id INTEGER NOT NULL,
    salary NUMERIC (8, 2) NOT NULL,
    manager_id INTEGER,
    department_id INTEGER,
    FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments (department_id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ON UPDATE CASCADE ON DELETE CASCADE
);
 
CREATE TABLE dependents (
    dependent_id SERIAL PRIMARY KEY,
    first_name CHARACTER VARYING (50) NOT NULL,
    last_name CHARACTER VARYING (50) NOT NULL,
    relationship CHARACTER VARYING (25) NOT NULL,
    employee_id INTEGER NOT NULL,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);